存储过程中的out,output,return 的使用 |
您所在的位置:网站首页 › return to 和return from › 存储过程中的out,output,return 的使用 |
output 类型的参数需要在存储过程的参数列表中添加。output类型的参数传入存储过程后还会返回其运行后的值。
return value类型的参数不需要在存储过程的参数列表中添加。return value类型的参数就是存储过程最后return的值。 数据库中使用output值和return值: ------------------------------------------------------- --数获存储过程中的OUTPUT参数和返回值获取 -------------------------------------------------------
CREATE PROCEDURE Proc_Test;1 @INPUT int, @OUTPUT int output AS BEGIN SET NOCOUNT ON;
SELECT @OUTPUT=@INPUT RETURN @INPUT+1
END GO
--调用output值和return返回值 DECLARE @OUT int,@RETURN int EXEC @RETURN=Proc_Test;1 0, @OUT output
SELECT [返回值]=@RETURN,[OUTPUT值]=@OUT
返回值 OUTPUT值 ----------- ----------- 1 0
----------------------------------------------------- -- SP_EXECUTESQL中的OUTPUT参数获取 ----------------------------------------------------- DECLARE @Para1 int,@Para2 int,@SUM int
EXECUTE SP_EXECUTESQL N'SELECT @SUM=@Para1+@Para2 ', N'@Para1 INT,@Para2 INT,@SUM INT OUTPUT ', 5,5,@SUM OUTPUT
SELECT [OUTPUT值]=@SUM
OUTPUT值 ----------- 10 ====================================================================== 下面在.net下调用存储过程: view plain copy to clipboard print ? 使用存储过程 #form1 { margin-left: 206px; } 暂无 暂无
view plain copy to clipboard print ? protected void ImageButton1_Click(object sender, ImageClickEventArgs e) { //定义数据库连接和SqlCommand对象 SqlConnection Conn=new SqlConnection(ConfigurationManager.ConnectionStrings["TestConnection"].ToString()); SqlCommand Cmd=new SqlCommand("Proc_Test;1",Conn); Cmd.CommandType = CommandType.StoredProcedure; //指定参数类型 SqlParameter input = Cmd.Parameters.Add("@INPUT", SqlDbType.Int); SqlParameter output = Cmd.Parameters.Add("@OUTPUT", SqlDbType.Int); SqlParameter return_ = Cmd.Parameters.Add("@RETURN", SqlDbType.Int); //指定参数方向 input.Direction = ParameterDirection.Input; output.Direction = ParameterDirection.Output; return_.Direction = ParameterDirection.ReturnValue; //参数赋值 if (Input.Text == "") { input.Value = 0; } else { input.Value = Convert.ToInt32(Input.Text); } //调用存储过程 Conn.Open(); Cmd.ExecuteNonQuery(); Conn.Close(); Output.Text = output.Value.ToString();//获取output值 Return.Text = return_.Value.ToString();//获取返回值 } 转载:http://www.cnblogs.com/lengbingshy/archive/2010/02/22/1671262.html
CREATE TABLE [dbo].[Order]( ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() 创建存储过程 CREATE proc pro_TestProcTwo ( @a int , @b int out, @c int output ) as set @a = @a*2; set @c = @a + 2; set @b = @a + @c; return @a+@b+@c; 执行存储过程 declare @b int,@c int,@return int exec @return = pro_TestProcTwo 10,@b out,@c output; select @b as 'out' ,@c as 'output', @return as 'return';
在c#中调用存储过程 string strRes = null; int result = 0; int _output = 0; int _return = 0; int _out = 0; try { using (SqlConnection myConnection = new SqlConnection(SqlHelper.myConnection)) { using (SqlCommand myCommand = new SqlCommand("pro_TestProcTwo", myConnection)) { myCommand.CommandType = CommandType.StoredProcedure; myCommand.Parameters.AddWithValue("@a", 10); SqlParameter outputOne = myCommand.Parameters.Add("@b", SqlDbType.Int); SqlParameter outPutTwo = myCommand.Parameters.Add("@c", SqlDbType.Int); outputOne.Direction = ParameterDirection.Output; outPutTwo.Direction = ParameterDirection.Output; SqlParameter t_return = myCommand.Parameters.Add("@return", SqlDbType.Int); t_return.Direction = ParameterDirection.ReturnValue; myConnection.Open(); myCommand.ExecuteNonQuery(); result = myCommand.ExecuteNonQuery(); _out = Convert.ToInt32(outputOne.Value); _output = Convert.ToInt32(outPutTwo.Value); _return = Convert.ToInt32(t_return.Value); } } } catch (Exception ee) { strRes = ee.Message.ToString(); } |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |